package cn.edu.sdjzu.xg.bysj.dao;


import cn.edu.sdjzu.xg.bysj.domain.School;
import cn.edu.sdjzu.xg.bysj.exception.BysjException;
import cn.edu.sdjzu.xg.bysj.util.Condition;
import cn.edu.sdjzu.xg.bysj.util.JdbcHelper;
import cn.edu.sdjzu.xg.bysj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;


public final class SchoolDao {
    private static SchoolDao schoolDao = null;

    //将唯一构造器私有，阻止本类外生成本类的对象
    private SchoolDao() {
    }

    //返回本类唯一的对象
    public static SchoolDao getInstance() {
        // 静态变量schoolDao为空时，才创建SchoolDao对象。
        // 第二次调用本方法schoolDao就不空了，不再创建对象。
        if (schoolDao == null) {
            schoolDao = new SchoolDao();
        }
        return schoolDao;
    }

    public static void main(String[] args) throws SQLException, BysjException {
//        Collection<School> allSchool = SchoolDao.getInstance().findAll(null);
//
//        System.out.println("--------");
//        System.out.println(allSchool);
//        System.out.println("--------");
//
//        Pagination pagination = new Pagination(2,3);
//        Collection<School> schools = SchoolDao.getInstance().findAll(pagination);
//        System.out.println(schools);

//        School school3 = new School("4","4","4");
//        System.out.println("school3 without id = " + school3);
//        SchoolService.getInstance().add(school3);
//        schools = SchoolDao.getInstance().findAll();
//        System.out.println(schools);

//        School school4 = SchoolDao.getInstance().find(4);
//        school4.setDescription("4444");
////        SchoolService.getInstance().update(school4);
//        school4 = SchoolDao.getInstance().find(4);
//        System.out.println("school4=" + school4);
//
//        school1 = SchoolDao.getInstance().find(2);
//        System.out.println("school1=" + school1);
//
//        boolean deleted4 = SchoolService.getInstance().delete(4);
//        schools = SchoolDao.getInstance().findAll(1);
//        System.out.println(schools);
//
//        boolean deleted3 = SchoolService.getInstance().delete(3);
//        schools = SchoolDao.getInstance().findAll(1);
//        System.out.println(schools);

    }

    public int count(Connection connection) throws SQLException {

        //准备预编译的语句
        String sql_count = "SELECT COUNT(id) as cnt FROM school";

        PreparedStatement pstmt_count =
                connection.prepareStatement(sql_count);

        int counter = 0;
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_count = pstmt_count.executeQuery();
        if (resultSet_count.next()) {
            counter = resultSet_count.getInt("cnt");
        }
        JdbcHelper.close(resultSet_count, pstmt_count);
        return counter;
    }

    /**
     * 返回id对应的School对象
     *
     * @param id 对象对应的id
     * @return School对象
     * @throws SQLException
     */
    public School find(Integer id) throws SQLException, BysjException {
        //创建School类型的引用，暂不创建对象
        School desiredSchool = null;
        //获得数据库连接
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM school WHERE id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()) {
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            desiredSchool = new School(id, description, no, remarks);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id, connection);
        return desiredSchool;
    }

    /**
     * 返回某页上的School对象
     * @param pagination 分页对象(页号, 每页面对象数)
     * @return School对象集合
     * @throws SQLException
     */
    public Collection<School> findAll(Pagination pagination, List<Condition> conditionList)
            throws SQLException, BysjException {
        //创建集合类对象，用来保存所有记录代表的School对象        
        Collection<School> desiredSchools = new HashSet<School>();
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();

        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM School ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取Teacher中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "School",conditionList);
        if(pagination != null){
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }


        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet.next()) {
            //获得school表中当前记录的id字段的值
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            //以当前记录的各个字段值为参数，创建School对象
            School desiredSchool = new School(id, description, no, remarks);
            //将当前记录代表的School对象添加到desiredSchools中
            desiredSchools.add(desiredSchool);
        }
        JdbcHelper.close(resultSet, pstmt_select_all, connection);
        return desiredSchools;
    }

    /**
     * 返回所有School对象
     *
//     * @return School对象集合
//     * @throws SQLException
//     * @param o
//     */
//    public Collection<School> findAll(Object o) throws SQLException {
//        return this.findAll(null);
//    }

    public boolean update(School school) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_update = "UPDATE school SET description = ?, no = ?, remarks = ? WHERE id = ?";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_update);

        pstmt_update.setString(1, school.getDescription());
        pstmt_update.setString(2, school.getNo());
        pstmt_update.setString(3, school.getRemarks());
        pstmt_update.setInt(4, school.getId());

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();

        JdbcHelper.close(pstmt_update, connection);
        return rowAffected > 0;
    }

    public boolean add(School school) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_insert = "INSERT INTO school (description, no, remarks) VALUES (?, ?, ?)";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_insert);

        pstmt_update.setString(1, school.getDescription());
        pstmt_update.setString(2, school.getNo());
        pstmt_update.setString(3, school.getRemarks());

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();

        JdbcHelper.close(pstmt_update, connection);
        return rowAffected > 0;
    }

    public boolean delete(Integer id) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_delete_id = "DELETE FROM school WHERE id = ?";
        PreparedStatement pstmt_delete =
                connection.prepareStatement(sql_delete_id);

        pstmt_delete.setInt(1, id);

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_delete.executeUpdate();

        JdbcHelper.close(pstmt_delete, connection);
        return rowAffected > 0;
    }

    public boolean delete(School school) throws SQLException {
        return this.delete(school.getId());
    }
}
